unit QExport4Xlsx;

{$I QExport4VerCtrl.inc}

interface

uses Classes, QExport4IniFiles, QExport4, QExport4BaseArchiveClass, QExport4Types
  {$IFNDEF NOGUI}, Graphics{$ELSE}, QExport4Graphics{$ENDIF},
  ExtCtrls, QXMLWriter,
  {$IFDEF VCL10}
    WideStrings,
  {$ELSE}
    QExport4EmsWideStrings,
  {$ENDIF}
  QExport4EmsWideStrUtils,
  QExport4MSOfficeCommon;

type
  TXlsxBorder = class(TPersistent)
  private
    FStyle: TXlsxBorderStyle;
    FColor: TColor;
  public
    constructor Create;
    procedure Assign(Source: TPersistent); override;
    procedure Default;
  published
    property Style: TXlsxBorderStyle read FStyle
      write FStyle default xbsThin;
    property Color: TColor read FColor write FColor
      default clBlack;
  end;

  TXlsxCellStyle = class;

  TXlsxStyleItem = class(TCollectionItem)
  private
    FOptions: TXlsxCellStyle;
    procedure SetOptions(const Value: TXlsxCellStyle);
  published
    constructor Create(Collection: TCollection); override;
    destructor Destroy; override;
    procedure Assign(Source: TPersistent); override;
    property Options: TXlsxCellStyle read FOptions
      write SetOptions;
  end;
  TXlsxStripStyle = TXlsxStyleItem;

  TXlsxStyleList = class(TCollection)
  protected
    FCellXfsCount: Integer;
    FNumFmtsCount: Integer;
    FFontsCount: Integer;
    FFillsCount: Integer;
    FBordersCount: Integer;
    function GetItem(Index: Integer): TXlsxStyleItem;
    procedure SetItem(Index: Integer; Value: TXlsxStyleItem);
  public
    constructor Create(ItemClass: TCollectionItemClass);
    function Add: TXlsxStyleItem;
    procedure AddList(Source: TPersistent);
    function CountByColName(AColName: string): Integer;
    function FindByColName(AColName: string): TXlsxStyleItem;
    function StyleIdByColName(AColName: string): Integer;
    property Items[Index: Integer]: TXlsxStyleItem read GetItem
      write SetItem; default;
  end;
  TXlsxStripStyleList = TXlsxStyleList;

  // dee
  TXlsxStyleType = (xlsxstDefault, xlsxstHeader, xslxstCaption, xlsxstData, xlsxstFooter,
    xlsxstInteger, xlsxstFloat, xlsxstDate, xlsxstTime, xlsxstDateTime, xlsxstCurrency,
    xlsxstStripCol, xlsxstStripRow);

  TXlsxCellStyle = class(TPersistent)
  private
    FFont: TFont;
    FBorder: TXlsxBorder;
    FBackgroundColor: TColor;
    FAlignment: TMSCellAlignment;
    FVerticalAligment: TMSCellVerticalAligment;
    FWrapText: boolean;
    FNumericFormat: string;
    FStyleID: Integer; // for StyleList only
    FColName: string;
    FNumFmtId: Integer;
    FFontId: Integer;
    FFillId: Integer;
    FBorderId: Integer;
    FUseBackground: Boolean;
    FUseBorder: Boolean;
    function GetIsDefault: Boolean;
    procedure SetDefault(const Value: Boolean);
    procedure SetFont(const Value: TFont);
    procedure SetBorder(const Value: TXlsxBorder);
    procedure SetNumericFormat(const Value: string);
    procedure SetStyleID(const Value: Integer);
  public
    constructor Create;
    destructor Destroy; override;
    procedure Assign(Source: TPersistent); override;

    procedure LoadFromIni(IniFile: TQIniFile; const Section: WideString);
    procedure SaveToIni(IniFile: TQIniFile; const Section: WideString);
    procedure Default; virtual;
    property IsDefault: Boolean read GetIsDefault write SetDefault;

    // TODO to published
    property NumericFormat: string read FNumericFormat write SetNumericFormat;
    property StyleID: Integer read FStyleID write SetStyleID; 
    //\

  published
    property Font: TFont read FFont write SetFont;

    property BackgroundColor: TColor read FBackgroundColor
      write FBackgroundColor default clWhite;
    property UseBackground: boolean read FUseBackground
      write FUseBackground default False;

    property Alignment: TMSCellAlignment read FAlignment
      write FAlignment default caLeft;
    property VerticalAligment: TMSCellVerticalAligment read FVerticalAligment
      write FVerticalAligment default cvaBottom;
    property WrapText: boolean read FWrapText
      write FWrapText default False;

    property UseBorder: boolean read FUseBorder
      write FUseBorder default False;
    property Border: TXlsxBorder read FBorder write SetBorder;
  end;

  TQExport4XlsxOptions = class(TPersistent)
  private
    FHolder: TPersistent;
    FHeaderStyle: TXlsxCellStyle;
    FFooterStyle: TXlsxCellStyle;
    FCaptionRowStyle: TXlsxCellStyle;
    FDataStyle: TXlsxCellStyle;
    FStripStyle: TMSStripStyleType;
    FStripStylesList: TXlsxStripStyleList;
    procedure SetHeaderStyle(const Value: TXlsxCellStyle);
    procedure SetCaptionStyle(const Value: TXlsxCellStyle);
    procedure SetDataStyle(const Value: TXlsxCellStyle);
    procedure SetStripStyles(const Value: TXlsxStripStyleList);
    procedure SetFooterStyle(const Value: TXlsxCellStyle);
  public
    constructor Create(Holder: TPersistent);
    destructor Destroy; override;
    procedure Assign(Source: TPersistent); override;
  published
    property HeaderStyle: TXlsxCellStyle read FHeaderStyle
      write SetHeaderStyle;
    property CaptionRowStyle: TXlsxCellStyle read FCaptionRowStyle
      write SetCaptionStyle;
    property DataStyle: TXlsxCellStyle read FDataStyle
      write SetDataStyle;
    property FooterStyle: TXlsxCellStyle read FFooterStyle
      write SetFooterStyle;
    property StripStyleType: TMSStripStyleType read FStripStyle
      write FStripStyle default ssNone;
    property StripStylesList: TXlsxStripStyleList read FStripStylesList
      write SetStripStyles;
  end;

  TXlsxFileMaker = class
  private
    FSharedList: {$IFDEF QE_UNICODE}TWideStringList{$ELSE}TStrings{$ENDIF};
    FExportStream: TStream;
    FExportWriter: TQXMLWriter;
    FXlsxOptions: TQExport4XlsxOptions;
    FWorkbookFormats: TQExportFormats;
    FStyleList: TXlsxStyleList;

    FZipper: TBaseArchiveClass;
    FTempDir: WideString;
    FSheetName: WideString;
    FExportFileName: WideString;
    function GetSharedString(Value: WideString): Integer;
    procedure SetSheetName(const Value: WideString);
    procedure CreateStyles;
  public
    constructor Create(const ExportFile: WideString;
      ExportOptions: TQExport4XlsxOptions; WorkbookFormats: TQExportFormats);
    destructor Destroy; override;

    procedure CreateDirsStructure;
    function IsExistDirsStructure: Boolean;
    procedure FillCommonData;
    procedure CompressFile;
    procedure AddCell(Col, Row, ColCount: Integer; Value: WideString;
      CellType: TQExportColType; CellStyle: Integer = -1);
    procedure PrepareExport;
    procedure FinishExport;

    property SheetName: WideString read FSheetName write SetSheetName;
    property ExportFileName: WideString read FExportFileName;
  end;

  TQExport4Xlsx = class(TQExport4AdvancedText)
  private
    FCounter: Integer;
    FDataRowCounter: Integer;
    FXlsxFile: TXlsxFileMaker;
    FXlsxOptions: TQExport4XlsxOptions;
    FSheetName: WideString;
    procedure SetXlsxOptions(const Value: TQExport4XlsxOptions);
    procedure SetSheetName(const Value: WideString);
  private
    procedure WriteHeader;
    procedure WriteFooter;
  protected
    procedure BeginExport; override;
    procedure BeforeExport; override;
    procedure AfterExport; override;
    procedure EndExport; override;

    procedure WriteCaptionRow; override;
    procedure WriteDataRow; override;
  public
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
    procedure Execute; override;
  published
    property AllowCaptions;
    property Captions;
    property Formats;
    property UserFormats;
    property SheetName: WideString read FSheetName write SetSheetName;
    property XlsxOptions: TQExport4XlsxOptions read FXlsxOptions write SetXlsxOptions;
  end;

procedure DrawXlsxCell(PaintBox: TPaintBox; CellStyle: TXlsxCellStyle);

implementation

uses
  SysUtils{$IFDEF VCL6}, StrUtils{$ENDIF}, QExport4Common
  {$IFDEF VER130}, FileCtrl{$ENDIF}
  {$IFDEF VCL6}, Variants{$ENDIF};

const
  LETTERS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  xlsxDefaultStylesCount = 5; // 1 "default" + 4 "Header, CaptionRow, Data, Footer"
  xlsxStyle_Default = 0;
  xlsxStyle_Header = 1;
  xlsxStyle_Footer = 2;
  xlsxStyle_Caption = 3;
  xlsxStyle_Data = 4;
  xlsxCustomNumericFormat = 164; // first custom format

var
  xlsxDefaultFont: TFont;

function Col2Letter(Col: integer): string;
var
  n, m, c: integer;
begin
  c := Col - 1;
  Result := EmptyStr;
  n := c div 26;
  m := c mod 26;
  if n > 0 then Result := Result + Copy(LETTERS, n, 1);
  Result := Result + Copy(LETTERS, m + 1, 1);
end;

procedure DrawXlsxCell(PaintBox: TPaintBox; CellStyle: TXlsxCellStyle);

  procedure FillBackground;
  begin
    if CellStyle.UseBackground then
      PaintBox.Canvas.Brush.Color := CellStyle.BackgroundColor
    else
      PaintBox.Canvas.Brush.Color := clWhite;
    PaintBox.Canvas.FillRect(Rect(0, 0, PaintBox.Width, PaintBox.Height));
  end;

const
  OutText = 'Aa Zz';
var
  X, Y: integer;
begin
  if not Assigned(CellStyle) then Exit;
  
  FillBackground;
  with PaintBox.Canvas.Font do
  begin
    Name := CellStyle.Font.Name;
    Size := CellStyle.Font.Size;
    Charset := CellStyle.Font.Charset;
    Color := CellStyle.Font.Color;
    Style := CellStyle.Font.Style;
  end;

  case CellStyle.Alignment of
    caLeft: X := 5;
    caCenter: X := (PaintBox.Width - PaintBox.Canvas.TextWidth(OutText)) div 2;
    caRight: X := PaintBox.Width - 5 - PaintBox.Canvas.TextWidth(OutText);
    else
      X := 0;
  end;

  case CellStyle.VerticalAligment of
    cvaTop: Y := 5;
    cvaMiddle: Y := (PaintBox.Height - PaintBox.Canvas.TextHeight(OutText)) div 2;
    cvaBottom: Y := PaintBox.Height - 5 - PaintBox.Canvas.TextHeight(OutText);
    else
      Y := 0;
  end;
  PaintBox.Canvas.TextOut(X, Y, OutText);
end;

{ TXlsxBorder }

constructor TXlsxBorder.Create;
begin
  Default;
end;

procedure TXlsxBorder.Assign(Source: TPersistent);
begin
  if Source is TXlsxBorder then
  begin
    Style := (Source as TXlsxBorder).Style;
    Color := (Source as TXlsxBorder).Color;
  end;
end;

procedure TXlsxBorder.Default;
begin
  FStyle := xbsThin;
  FColor := clBlack;
end;

{ TXlsxStyleItem }

procedure TXlsxStyleItem.SetOptions(const Value: TXlsxCellStyle);
begin
  FOptions.Assign(Value);
end;

constructor TXlsxStyleItem.Create(Collection: TCollection);
begin
  inherited Create(Collection);
  FOptions := TXlsxCellStyle.Create;
end;

destructor TXlsxStyleItem.Destroy;
begin
  FOptions.Free;
  inherited Destroy;
end;

procedure TXlsxStyleItem.Assign(Source: TPersistent);
begin
  FOptions.Assign(TXlsxStyleItem(Source).Options);
end;

{ TXlsxStyleList }

constructor TXlsxStyleList.Create(ItemClass: TCollectionItemClass);
begin
  inherited Create(ItemClass);
  FNumFmtsCount := 0;
  FFontsCount := 0;
  FFillsCount := 0;
  FBordersCount := 0;
end;

function TXlsxStyleList.GetItem(Index: Integer): TXlsxStyleItem;
begin
  Result := TXlsxStyleItem(inherited Items[Index]);
end;

procedure TXlsxStyleList.SetItem(Index: Integer;
  Value: TXlsxStyleItem);
begin
  inherited Items[Index] := Value;
end;

function TXlsxStyleList.Add: TXlsxStyleItem;
begin
  Result := TXlsxStyleItem(inherited Add)
end;

function TXlsxStyleList.CountByColName(AColName: string): Integer;
var
  I: Integer;
begin
  Result := 0;
  for I := 0 to Count - 1 do
    if QECompareStr(Items[I].Options.FColName,AColName) = 0 then
      Inc(Result);
end;

function TXlsxStyleList.FindByColName(AColName: string): TXlsxStyleItem;
var
  I: Integer;
begin
  Result := nil;
  for I := 0 to Count - 1 do
    if QECompareStr(Items[I].Options.FColName,AColName) = 0 then
    begin
      Result := Items[i];
      Exit;
    end;
end;

function TXlsxStyleList.StyleIdByColName(AColName: string): Integer;
var
  I: Integer;
begin
  Result := -1;
  for I := 0 to Count - 1 do
    if QECompareStr(Items[I].Options.FColName, AColName) = 0 then
    begin
      Result := Items[I].Options.StyleID;
      Exit;
    end;
end;

procedure TXlsxStyleList.AddList(Source: TPersistent);
var
  I: Integer;
begin
  if Source is TCollection then
  begin
    BeginUpdate;
    try
      for I := 0 to TCollection(Source).Count - 1 do
        Add.Assign(TCollection(Source).Items[I]);
    finally
      EndUpdate;
    end;
    Exit;
  end;
end;

{ TXlsxCellStyle }

function TXlsxCellStyle.GetIsDefault: Boolean;
begin
  Result := (FFont.Name = xlsxDefaultFont.Name)
    and (FFont.Size = xlsxDefaultFont.Size)
    and (FFont.Style = xlsxDefaultFont.Style)
    and (FFont.Color = xlsxDefaultFont.Color)
    and (FBackgroundColor = clWhite)
    and (FUseBackground = False)
    and (FAlignment = caLeft)
    and (FVerticalAligment = cvaBottom)
    and (not FWrapText)
    and (FNumericFormat = '');
end;

procedure TXlsxCellStyle.SetDefault(const Value: Boolean);
begin
  if Value then
    Default;
end;

procedure TXlsxCellStyle.SetFont(const Value: TFont);
begin
  FFont.Assign(Value);
end;

procedure TXlsxCellStyle.SetNumericFormat(const Value: string);
begin
  FNumericFormat := Value;
end;

procedure TXlsxCellStyle.SetStyleID(const Value: Integer);
begin
  Assert( (FStyleID > -1), 'StyleID is not empty' );
  FStyleID := Value;
end;

procedure TXlsxCellStyle.SetBorder(const Value: TXlsxBorder);
begin
  FBorder.Assign(Value);
end;

constructor TXlsxCellStyle.Create;
begin
  FFont := TFont.Create;
  FBorder := TXlsxBorder.Create;
  Default;
end;

destructor TXlsxCellStyle.Destroy;
begin
  FBorder.Free;
  FFont.Free;
  inherited;
end;

procedure TXlsxCellStyle.Assign(Source: TPersistent);
begin
  if Source is TXlsxCellStyle then
  begin
    Font := TXlsxCellStyle(Source).Font;
    UseBackground := TXlsxCellStyle(Source).UseBackground;
    BackgroundColor := TXlsxCellStyle(Source).BackgroundColor;
    Alignment := TXlsxCellStyle(Source).Alignment;
    VerticalAligment := TXlsxCellStyle(Source).VerticalAligment;
    WrapText := TXlsxCellStyle(Source).WrapText;
    UseBorder := TXlsxCellStyle(Source).UseBorder;
    Border.Assign(TXlsxCellStyle(Source).Border);
    NumericFormat := TXlsxCellStyle(Source).NumericFormat;

    FStyleID := TXlsxCellStyle(Source).FStyleID;
    FNumFmtId := TXlsxCellStyle(Source).FNumFmtId;
    FFontId := TXlsxCellStyle(Source).FFontId;
    FFillId := TXlsxCellStyle(Source).FFillId;
    FBorderId := TXlsxCellStyle(Source).FBorderId;
  end;
end;

procedure TXlsxCellStyle.LoadFromIni(IniFile: TQIniFile;
  const Section: WideString);
begin
  with IniFile do
  begin
    Font.Name := ReadString(Section, S_XLSX_FontName, xlsxDefaultFont.Name);
    Font.Size := ReadInteger(Section, S_XLSX_FontSize, xlsxDefaultFont.Size);
    Font.Color := ReadInteger(Section, S_XLSX_FontColor, xlsxDefaultFont.Color);
    if ReadBool(Section, S_XLSX_FontBold, False) then
      Font.Style := Font.Style + [fsBold]
    else
      Font.Style := Font.Style - [fsBold];
    if ReadBool(Section, S_XLSX_FontItalic, False) then
      Font.Style := Font.Style + [fsItalic]
    else
      Font.Style := Font.Style - [fsItalic];
    if ReadBool(Section, S_XLSX_FontUnderline, False) then
      Font.Style := Font.Style + [fsUnderline]
    else
      Font.Style := Font.Style - [fsUnderline];
    UseBackground := ReadBool(Section, S_XLSX_UseBackground, False);
    BackgroundColor := ReadInteger(Section, S_XLSX_BackgroundColor, clBlack);
    Alignment := TMSCellAlignment(ReadInteger(Section, S_XLSX_HorAlignment, 0));
    VerticalAligment := TMSCellVerticalAligment(ReadInteger(Section, S_XLSX_VertAlignment, 0));
    UseBorder := ReadBool(Section, S_XLSX_UseBorder, False);
    Border.Style := TXlsxBorderStyle(ReadInteger(Section, S_XLSX_BorderStyle, 0));
    Border.Color := ReadInteger(Section, S_XLSX_BorderColor, clBlack);
    NumericFormat := ReadString(Section, S_XLSX_NumericFormat, '');
  end;
end;

procedure TXlsxCellStyle.SaveToIni(IniFile: TQIniFile;
  const Section: WideString);
begin
  with IniFile do
  begin
    WriteString(Section, S_XLSX_FontName, Font.Name);
    WriteInteger(Section, S_XLSX_FontSize, Font.Size);
    WriteInteger(Section, S_XLSX_FontColor, Font.Color);
    if fsBold in Font.Style then
      WriteBool(Section, S_XLSX_FontBold, true)
    else
      WriteBool(Section, S_XLSX_FontBold, false);
    if fsItalic in Font.Style then
      WriteBool(Section, S_XLSX_FontItalic, true)
    else
      WriteBool(Section, S_XLSX_FontItalic, false);
    if fsUnderline in Font.Style then
      WriteBool(Section, S_XLSX_FontUnderline, true)
    else
      WriteBool(Section, S_XLSX_FontUnderline, false);
    WriteBool(Section, S_XLSX_UseBackground, UseBackground);
    WriteInteger(Section, S_XLSX_BackgroundColor, BackgroundColor);
    WriteInteger(Section, S_XLSX_HorAlignment, Integer(Alignment));
    WriteInteger(Section, S_XLSX_VertAlignment, Integer(VerticalAligment));
    WriteBool(Section, S_XLSX_UseBorder, UseBorder);
    WriteInteger(Section, S_XLSX_BorderStyle, Integer(Border.Style));
    WriteInteger(Section, S_XLSX_BorderColor, Border.Color);
    WriteString(Section, S_XLSX_NumericFormat, NumericFormat);
  end;
end;

procedure TXlsxCellStyle.Default;
begin
  FStyleID := -1;  // for StyleList only
  FNumFmtId := -1;
  FFontId := -1;
  FFillId := -1;
  FBorderId := -1;

  FFont.Name := xlsxDefaultFont.Name;
  FFont.Size := xlsxDefaultFont.Size;
  FFont.Style := xlsxDefaultFont.Style;
  FFont.Color := xlsxDefaultFont.Color;
  FBackgroundColor := clWhite;
  FUseBackground := False;
  FAlignment := caLeft;
  FVerticalAligment := cvaBottom;
  FWrapText := False;
  FUseBorder := False;
  FBorder.Default;
  FNumericFormat := '';
end;

{ TQExport4XlsxOptions }

procedure TQExport4XlsxOptions.SetHeaderStyle(const Value: TXlsxCellStyle);
begin
  FHeaderStyle.Assign(Value);
end;

procedure TQExport4XlsxOptions.SetCaptionStyle(
  const Value: TXlsxCellStyle);
begin
  FCaptionRowStyle.Assign(Value);
end;

procedure TQExport4XlsxOptions.SetDataStyle(const Value: TXlsxCellStyle);
begin
  FDataStyle.Assign(Value);
end;

procedure TQExport4XlsxOptions.SetStripStyles(
  const Value: TXlsxStripStyleList);
begin
  FStripStylesList.Assign(Value);
end;

procedure TQExport4XlsxOptions.SetFooterStyle(const Value: TXlsxCellStyle);
begin
  FFooterStyle.Assign(Value);
end;


constructor TQExport4XlsxOptions.Create(Holder: TPersistent);
begin
  inherited Create;
  FHolder := Holder;
  FHeaderStyle := TXlsxCellStyle.Create;
  FCaptionRowStyle := TXlsxCellStyle.Create;
  FDataStyle := TXlsxCellStyle.Create;
  FFooterStyle := TXlsxCellStyle.Create;

  FStripStyle := ssNone;
  FStripStylesList := TXlsxStripStyleList.Create(TXlsxStripStyle);
end;

destructor TQExport4XlsxOptions.Destroy;
begin
  FHolder := nil;
  FHeaderStyle.Free;
  FCaptionRowStyle.Free;
  FDataStyle.Free;
  FStripStylesList.Free;
  FFooterStyle.Free;
  inherited;
end;

procedure TQExport4XlsxOptions.Assign(Source: TPersistent);
begin
  if Source is TQExport4XlsxOptions then
  begin
    HeaderStyle := TQExport4XlsxOptions(Source).HeaderStyle;
    CaptionRowStyle := TQExport4XlsxOptions(Source).CaptionRowStyle;
    DataStyle := TQExport4XlsxOptions(Source).DataStyle;
    FooterStyle := TQExport4XlsxOptions(Source).FooterStyle;
    StripStyleType := TQExport4XlsxOptions(Source).StripStyleType;
    StripStylesList := TQExport4XlsxOptions(Source).StripStylesList;
  end;
end;

{ TXlsxMaker }

constructor TXlsxFileMaker.Create(const ExportFile: WideString;
  ExportOptions: TQExport4XlsxOptions; WorkbookFormats: TQExportFormats);
begin
  FSharedList := {$IFDEF QE_UNICODE}TWideStringList.Create{$ELSE}TStringList.Create{$ENDIF};
  FExportFileName := ExportFile;
  FTempDir := ExtractFileDir(ParamStr(0)) + '\Temp';
  FSheetName := 'sheet1';
//  if Assigned(ExportOptions) then // dee
    FXlsxOptions := ExportOptions;
  FWorkbookFormats := WorkbookFormats;
  FStyleList := TXlsxStyleList.Create(TXlsxStyleItem);
end;

destructor TXlsxFileMaker.Destroy;
begin
//  if Assigned(FXlsxOptions) then
//  begin
////dee
//    FXlsxOptions.Free;
//    FXlsxOptions := nil;
//  end;
  FSharedList.Free;
  FStyleList.Free;
  inherited;
end;

function TXlsxFileMaker.GetSharedString(Value: WideString): Integer;
begin
  FSharedList.Add(Value);
  Result := FSharedList.Count - 1;
end;

procedure TXlsxFileMaker.SetSheetName(const Value: WideString);
begin
  if Value <> '' then
    FSheetName := Value;
end;

procedure TXlsxFileMaker.CreateDirsStructure;
begin
  ForceDirectories(FTempDir);
  ForceDirectories(FTempDir + '\xl');
  ForceDirectories(FTempDir + '\xl\_rels');
  ForceDirectories(FTempDir + '\xl\theme');
  ForceDirectories(FTempDir + '\xl\worksheets');
  ForceDirectories(FTempDir + '\docProps');
  ForceDirectories(FTempDir + '\_rels');
end;

function TXlsxFileMaker.IsExistDirsStructure: Boolean;
begin
  Result := DirectoryExists(FTempDir)
    and DirectoryExists(FTempDir + '\xl')
    and DirectoryExists(FTempDir + '\xl\_rels')
    and DirectoryExists(FTempDir + '\xl\theme')
    and DirectoryExists(FTempDir + '\xl\worksheets')
    and DirectoryExists(FTempDir + '\docProps')
    and DirectoryExists(FTempDir + '\_rels');
end;

procedure TXlsxFileMaker.FillCommonData;
var
  Stream: TStream;
begin
  //FTempDir + '\_rels\.rels'
  Stream := TFileStream.Create(FTempDir + '\_rels\.rels', fmCreate);
  try
    with TQXMLWriter.Create(Stream) do
      try
        CreateProcessingInstruction('1.0', 'UTF-8', 'yes');
        BeginNode('Relationships', ['xmlns'], ['http://schemas.openxmlformats.org/package/2006/relationships']);
        CreateFullNode('Relationship', ['Id', 'Type', 'Target'], ['rId3', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties', 'docProps/app.xml']);
        CreateFullNode('Relationship', ['Id', 'Type', 'Target'], ['rId2', 'http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties', 'docProps/core.xml']);
        CreateFullNode('Relationship', ['Id', 'Type', 'Target'], ['rId1', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument', 'xl/workbook.xml']);
        EndNode();
      finally
        Free;
      end;
  finally
    Stream.Free;
  end;

  //FTempDir + '\docProps\app.xml'
  Stream := TFileStream.Create(FTempDir + '\docProps\app.xml', fmCreate);
  try
    with TQXMLWriter.Create(Stream) do
      try
        CreateProcessingInstruction('1.0', 'UTF-8', 'yes');
        BeginNode('Properties', ['xmlns', 'xmlns:vt'], ['http://schemas.openxmlformats.org/officeDocument/2006/extended-properties', 'http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes']);
        CreateFullNode('Application', 'Microsoft Excel');
        CreateFullNode('DocSecurity', '0');
        CreateFullNode('ScaleCrop', 'false');
        BeginNode('HeadingPairs');
        BeginNode('vt:vector', ['size', 'baseType'], ['2', 'variant']);
        BeginNode('vt:variant');
        CreateFullNode('vt:lpstr', 'Worksheets');
        EndNode();
        BeginNode('vt:variant');
        CreateFullNode('vt:i4', '1');
        EndNode();
        EndNode();
        EndNode();
        BeginNode('TitlesOfParts');
        BeginNode('vt:vector', ['size', 'baseType'], ['1', 'lpstr']);
        CreateFullNode('vt:lpstr', FSheetName);
        EndNode();
        EndNode();
        CreateFullNode('Company');
        CreateFullNode('LinksUpToDate', 'false');
        CreateFullNode('SharedDoc', 'false');
        CreateFullNode('HyperlinksChanged', 'false');
        CreateFullNode('AppVersion', '12.0000');
        EndNode();
      finally
        Free;
      end;
  finally
    Stream.Free;
  end;

  //FTempDir + '\docProps\core.xml'
  Stream := TFileStream.Create(FTempDir + '\docProps\core.xml', fmCreate);
  try
    with TQXMLWriter.Create(Stream) do
      try
        CreateProcessingInstruction('1.0', 'UTF-8', 'yes');
        BeginNode('cp:coreProperties', ['xmlns:cp', 'xmlns:dc', 'xmlns:dcterms', 'xmlns:dcmitype', 'xmlns:xsi'],
          ['http://schemas.openxmlformats.org/package/2006/metadata/core-properties', 'http://purl.org/dc/elements/1.1/',
          'http://purl.org/dc/terms/', 'http://purl.org/dc/dcmitype/', 'http://www.w3.org/2001/XMLSchema-instance']);
        CreateFullNode('dc:creator', 'User');
        CreateFullNode('cp:lastModifiedBy', 'User');
        CreateFullNode('dcterms:created', ['xsi:type'], ['dcterms:W3CDTF'], '2006-10-02T04:59:59Z');
        CreateFullNode('dcterms:modified', ['xsi:type'], ['dcterms:W3CDTF'], '2006-10-02T05:00:35Z');
        EndNode();
      finally
        Free;
      end;
  finally
    Stream.Free;
  end;

  //FTempDir + '\xl\_rels\workbook.xml.rels'
  Stream := TFileStream.Create(FTempDir + '\xl\_rels\workbook.xml.rels', fmCreate);
  try
    with TQXMLWriter.Create(Stream) do
      try
        CreateProcessingInstruction('1.0', 'UTF-8', 'yes');
        BeginNode('Relationships', ['xmlns'], ['http://schemas.openxmlformats.org/package/2006/relationships']);
        CreateFullNode('Relationship', ['Id', 'Type', 'Target'], ['rId3', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles', 'styles.xml']);
        CreateFullNode('Relationship', ['Id', 'Type', 'Target'], ['rId1', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet', 'worksheets/' + FSheetName + '.xml']);
        CreateFullNode('Relationship', ['Id', 'Type', 'Target'], ['rId4', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings', 'sharedStrings.xml']);
        EndNode();
      finally
        Free;
      end;
  finally
    Stream.Free;
  end;

  //FTempDir + '\xl\worksheets\' + FSheetName + '.xml'
  //DO it into BeginExport procedure
  
  //FTempDir + '\xl\styles.xml'

//  if Assigned(FXlsxOptions) then // dee
    CreateStyles;

  //FTempDir + '\xl\workbook.xml'
  Stream := TFileStream.Create(FTempDir + '\xl\workbook.xml', fmCreate);
  try
    with TQXMLWriter.Create(Stream) do
      try
        CreateProcessingInstruction('1.0', 'UTF-8', 'yes');
        BeginNode('workbook', ['xmlns', 'xmlns:r'], ['http://schemas.openxmlformats.org/spreadsheetml/2006/main', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships']);
        CreateFullNode('fileVersion ', ['lastEdited', 'lowestEdited', 'rupBuild'], ['4', '4', '4017']);
        CreateFullNode('workbookPr', ['defaultThemeVersion'], ['123820']);
        BeginNode('bookViews');
        CreateFullNode('workbookView', ['xWindow', 'yWindow', 'windowWidth', 'windowHeight'], ['120', '105', '14175', '7365']);
        EndNode();
        BeginNode('sheets');
        CreateFullNode('sheet', ['name', 'sheetId', 'r:id'], [FSheetName, '1', 'rId1']);
        EndNode();
        CreateFullNode('calcPr', ['calcId'], ['122211']);
        CreateFullNode('webPublishing', ['codePage'], ['1251']);
        EndNode();
      finally
        Free;
      end;
  finally
    Stream.Free;
  end;

  //FTempDir + '\[Content_Types].xml'
  Stream := TFileStream.Create(FTempDir + '\[Content_Types].xml', fmCreate);
  try
    with TQXMLWriter.Create(Stream) do
      try
        CreateProcessingInstruction('1.0', 'UTF-8', 'yes');
        BeginNode('Types', ['xmlns'], ['http://schemas.openxmlformats.org/package/2006/content-types']);
        CreateFullNode('Override', ['PartName', 'ContentType'], ['/xl/theme/theme1.xml', 'application/vnd.openxmlformats-officedocument.theme+xml']);
        CreateFullNode('Override', ['PartName', 'ContentType'], ['/xl/styles.xml', 'application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml']);
        CreateFullNode('Default', ['Extension', 'ContentType'], ['rels', 'application/vnd.openxmlformats-package.relationships+xml']);
        CreateFullNode('Default', ['Extension', 'ContentType'], ['xml', 'application/xml']);
        CreateFullNode('Override', ['PartName', 'ContentType'], ['/xl/workbook.xml', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml']);
        CreateFullNode('Override', ['PartName', 'ContentType'], ['/docProps/app.xml', 'application/vnd.openxmlformats-officedocument.extended-properties+xml']);
        CreateFullNode('Override', ['PartName', 'ContentType'], ['/xl/worksheets/' + FSheetName + '.xml', 'application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml']);
        CreateFullNode('Override', ['PartName', 'ContentType'], ['/xl/sharedStrings.xml', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml']);
        CreateFullNode('Override', ['PartName', 'ContentType'], ['/docProps/core.xml', 'application/vnd.openxmlformats-package.core-properties+xml']);
        EndNode();
      finally
        Free;
      end;
  finally
    Stream.Free;
  end;
end;

procedure TXlsxFileMaker.CompressFile;
begin
  FZipper := TBaseArchiveClass.Create;
  try
    FZipper.FileName := ExportFileName;
    FZipper.Compress;
  finally
    FZipper.Free;
  end;
end;

procedure TXlsxFileMaker.AddCell(Col, Row, ColCount: Integer; Value: WideString;
  CellType: TQExportColType; CellStyle: Integer = -1);
begin
  with FExportWriter do
  begin
    if Col = 0 then
      BeginNode('row', ['r', 'spans'], [IntToStr(Row), '1:'+IntToStr(ColCount)]);

    if CellType in [ectString, ectBoolean, ectUnknown] then
    begin
      if CellStyle > -1 then
        BeginNode('c', ['r', 's', 't'], [Col2Letter(Col + 1) + IntToStr(Row), IntToStr(CellStyle), 's'])
      else
        BeginNode('c', ['r', 't'], [Col2Letter(Col + 1) + IntToStr(Row), 's']);

      if Value <> '' then
        CreateFullNode('v', IntToStr(GetSharedString(ReplaceSymbols(Value))))
      else
        CreateFullNode('v');

    end
    else
    begin
      if CellStyle > -1 then
        BeginNode('c', ['r', 's'], [Col2Letter(Col + 1) + IntToStr(Row), IntToStr(CellStyle)])
      else
        BeginNode('c', ['r'], [Col2Letter(Col + 1) + IntToStr(Row)]);

      if Value <> '' then
//        CreateFullNode('v', ReplaceSymbols(Value))
        CreateFullNode('v', Value)
      else
        CreateFullNode('v');

    end;
    EndNode(); // c

    if Col = (ColCount-1) then
      EndNode(); // row
  end;
end;

procedure TXlsxFileMaker.PrepareExport;
begin
  //FTempDir + '\xl\worksheets\' + FSheetName + '.xml'
  FExportStream := TFileStream.Create(FTempDir + '\xl\worksheets\' + FSheetName + '.xml', fmCreate);
  FExportWriter := TQXMLWriter.Create(FExportStream);
  with FExportWriter do
  begin
    CreateProcessingInstruction('1.0', 'UTF-8', 'yes');
    BeginNode('worksheet', ['xmlns', 'xmlns:r'], ['http://schemas.openxmlformats.org/spreadsheetml/2006/main', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships']);
    CreateFullNode('dimension', ['ref'], ['A1']);
    BeginNode('sheetViews');
    BeginNode('sheetView', ['tabSelected', 'workbookViewId'], ['1', '0']);
    CreateFullNode('selection');
    EndNode();
    EndNode();
    CreateFullNode('sheetFormatPr', ['defaultRowHeight'], ['15']);
    BeginNode('sheetData');
  end;

end;

procedure TXlsxFileMaker.FinishExport;
var
  Stream: TStream;
  i: Integer;
begin
  Stream := TFileStream.Create(FTempDir + '\xl\sharedStrings.xml', fmCreate);
  try
    with TQXMLWriter.Create(Stream) do
      try
        CreateProcessingInstruction('1.0', 'UTF-8', 'yes');
        BeginNode('sst', ['xmlns', 'count', 'uniqueCount'], ['http://schemas.openxmlformats.org/spreadsheetml/2006/main', IntToStr(FSharedList.Count), IntToStr(FSharedList.Count)]);
        for i := 0 to FSharedList.Count - 1 do
        begin
          BeginNode('si');
          CreateFullNode('t', FSharedList[i]);
          EndNode();
        end;
        EndNode();
      finally
        Free;
      end;
  finally
    Stream.Free;
  end;

  //FTempDir + '\xl\worksheets\' + FSheetName +'.xml'
  with FExportWriter do
  begin
    EndNode(); //sheetData
    CreateFullNode('printOptions');
    CreateFullNode('pageMargins', ['left', 'right', 'top', 'bottom', 'header', 'footer'], ['0.7', '0.7', '0.75', '0.75', '0.3', '0.3']);
    CreateFullNode('headerFooter');
    EndNode(); // worksheet
  end;
  FExportWriter.Free;
  FExportStream.Free;
end;

procedure TXlsxFileMaker.CreateStyles;
var
  Stream: TStream;
  Writer: TQXMLWriter;
  i,k: Integer;

  procedure PrepareStyleList;

    procedure GetColumnFormat(ACol: TQExportColumn;
      var AFormat: string; var AFmtID: Integer);
    begin
      AFormat := ACol.Format;
      // dee: need to verify a match, the format of an Excel column
      // 
//      case ACol.ColType of
//        ectInteger, ectBigint:
//          if QECompareStr(AFormat, FWorkbookFormats.IntegerFormat) = 0 then
//            AFmtID := 1;
//        ectFloat:
//          if QECompareStr(AFormat, FWorkbookFormats.FloatFormat) = 0 then
//            AFmtID := 2;
//        ectDate:
//          if QECompareStr(AFormat, FWorkbookFormats.DateFormat) = 0 then
//            AFmtID := 14;
//        ectTime:
//          if QECompareStr(AFormat, FWorkbookFormats.TimeFormat) = 0 then
//            AFmtID := 21;
//        ectDateTime:
//          if QECompareStr(AFormat, FWorkbookFormats.DateTimeFormat) = 0 then
//            AFmtID := 22;
//        ectCurrency:
//          if QECompareStr(AFormat, FWorkbookFormats.CurrencyFormat) = 0 then
//              AFmtID := 44;
//      end;
    end;

    function Font2String(AFont: TFont): string;
    begin
      Result := AFont.Name
        + '|' + IntToStr(AFont.Size)
        + '|' + IntToStr(AFont.Charset)
        + '|' + ColorToHex(AFont.Color);
      if fsBold in AFont.Style then Result := Result + '|' + 'b';
      if fsItalic in AFont.Style then Result := Result + '|' + 'i';
      if fsUnderline in AFont.Style then Result := Result + '|' + 'u';
    end;
    function Border2String(ABorder: TXlsxBorder): string;
    begin
      Result := IntToStr( Integer(ABorder.Style))
        +'|'+ ColorToHex(ABorder.Color);
    end;
    function NumFmts2String(AStyle: TXlsxCellStyle; AFull: Boolean = true): string;
    begin
      Result := AStyle.NumericFormat;
      if AFull then Result := Result + '=' + IntToStr(AStyle.FNumFmtId);
    end;
    function CellXfs2String(AStyle: TXlsxCellStyle; AFull: Boolean = true): string;
    begin
      Result := IntToStr(AStyle.FNumFmtId)
      + '|' + IntToStr(AStyle.FFontId)
      + '|' + IntToStr(AStyle.FFillId)
      + '|' + IntToStr(AStyle.FBorderId);
      if AStyle.Alignment <> caLeft then Result := Result + '|' + 'h';
      if AStyle.VerticalAligment <> cvaBottom then Result := Result + '|' + 'v';
      if AStyle.WrapText then Result := Result + '|' + 'w';
      if AFull then
        Result := Result + '=' + IntToStr(AStyle.FStyleID);
    end;

    procedure AddItem(AStyle:TXlsxCellStyle; AColumn:TQExportColumn);
    var
      Item: TXlsxStyleItem;
    begin
      Item := FStyleList.Add;
      Item.Options.Assign(AStyle);
      Item.Options.FNumericFormat := AColumn.Format;
      Item.Options.FColName := AColumn.Name;
    end;

  var
    I, J: Integer;
    StrList: TStrings;
  begin
    // Default 0
    FStyleList.Add.Options.Font := xlsxDefaultFont;
    // Header 1, Footer 2, CaptionRow 3, DataRow 4
    FStyleList.Add.Options.Assign(FXlsxOptions.HeaderStyle);
    FStyleList.Add.Options.Assign(FXlsxOptions.FooterStyle);
    FStyleList.Add.Options.Assign(FXlsxOptions.CaptionRowStyle);
    FStyleList.Add.Options.Assign(FXlsxOptions.DataStyle);

    if (FXlsxOptions.StripStylesList.Count > 0)
      and (FXlsxOptions.StripStyleType <> ssNone) then
          if FXlsxOptions.StripStyleType = ssColumn then
            for I := 0 to TQExport4Xlsx(FXlsxOptions.FHolder).ExportRow.Columns.Count - 1 do
            begin
              AddItem(
                FXlsxOptions.StripStylesList[i mod FXlsxOptions.StripStylesList.Count].Options,
                TQExport4Xlsx(FXlsxOptions.FHolder).ExportRow.Columns[i]
              );
            end
          else
            for J := 0 to FXlsxOptions.StripStylesList.Count - 1 do
              for I := 0 to TQExport4Xlsx(FXlsxOptions.FHolder).ExportRow.Columns.Count - 1 do
                begin
                  AddItem(
                    FXlsxOptions.StripStylesList[J].Options,
                    TQExport4Xlsx(FXlsxOptions.FHolder).ExportRow.Columns[i]
                  );
                end
    else
      for I := 0 to TQExport4Xlsx(FXlsxOptions.FHolder).ExportRow.Columns.Count - 1 do
        if not (TQExport4Xlsx(FXlsxOptions.FHolder).ExportRow.Columns[i].ColType
          in [ectString, ectBoolean, ectUnknown]) then
        begin
            AddItem(
              FXlsxOptions.FDataStyle,
              TQExport4Xlsx(FXlsxOptions.FHolder).ExportRow.Columns[i]
            );
        end;

    StrList := TStringList.Create;
    try
      // Fonts
      FStyleList.FFontsCount := 0;
      for I := 0 to FStyleList.Count - 1 do
      begin
        j := StrList.IndexOf( Font2String(FStyleList[i].Options.Font) );
        if j > -1 then
          FStyleList[i].Options.FFontId := j
        else
        begin
          FStyleList[i].Options.FFontId := FStyleList.FFontsCount;
          StrList.Add( Font2String(FStyleList[i].Options.Font ));
          Inc(FStyleList.FFontsCount);
        end;
      end;
      // Fills
      StrList.Clear;
      FStyleList.FFillsCount := 1; // 2 default value 'none' and 'gray125'
      for I := 0 to FStyleList.Count - 1 do
      begin
        j := StrList.IndexOf( ColorToHex(FStyleList[i].Options.BackgroundColor ));
        if j > -1 then
        begin
          FStyleList[i].Options.FFillId := j;
          if j > 0 then
            Inc( FStyleList[i].Options.FFillId);
        end
        else
        begin
          FStyleList[i].Options.FFillId := FStyleList.FFillsCount;
          StrList.Add( ColorToHex(FStyleList[i].Options.BackgroundColor ));
          Inc(FStyleList.FFillsCount);
        end;
      end;
      // Borders
      StrList.Clear;
      FStyleList.FBordersCount := 0;
      for I := 0 to FStyleList.Count - 1 do
      begin
        j := StrList.IndexOf( Border2String(FStyleList[i].Options.Border ));
        if j > -1 then
          FStyleList[i].Options.FBorderId := j
        else
        begin
          FStyleList[i].Options.FBorderId := FStyleList.FBordersCount;
          StrList.Add( Border2String(FStyleList[i].Options.Border ));
          Inc(FStyleList.FBordersCount);
        end;
      end;
      // numFmts
      StrList.Clear;
      FStyleList.FNumFmtsCount := 0;
      for I := 0 to FStyleList.Count - 1 do
      begin
        if (FStyleList[i].Options.NumericFormat <> '') then
        begin
          if (FStyleList[i].Options.FNumFmtId = -1) then
          begin
            j := StrList.IndexOfName( NumFmts2String(FStyleList[i].Options, false ));
            if j > -1 then
            begin
              FStyleList[i].Options.FNumFmtId :=
                StrToInt( StrList.Values[ NumFmts2String(FStyleList[i].Options,false )] );
            end
            else
            begin
              FStyleList[i].Options.FNumFmtId := FStyleList.FNumFmtsCount + xlsxCustomNumericFormat;
              Inc(FStyleList.FNumFmtsCount);
              StrList.Add( NumFmts2String(FStyleList[i].Options ));
            end;
          end;
        end else
          FStyleList[i].Options.FNumFmtId := 0;
      end;

      // Styles
      StrList.Clear;
      FStyleList[xlsxStyle_Default].Options.FStyleID := xlsxStyle_Default;
      StrList.Add( CellXfs2String( FStyleList[xlsxStyle_Default].Options ));
      FStyleList[xlsxStyle_Header].Options.FStyleID := xlsxStyle_Header;
      StrList.Add( CellXfs2String( FStyleList[xlsxStyle_Header].Options ));
      FStyleList[xlsxStyle_Footer].Options.FStyleID := xlsxStyle_Footer;
      StrList.Add( CellXfs2String( FStyleList[xlsxStyle_Footer].Options ));
      FStyleList[xlsxStyle_Caption].Options.FStyleID := xlsxStyle_Caption;
      StrList.Add( CellXfs2String( FStyleList[xlsxStyle_Caption].Options ));
      FStyleList[xlsxStyle_Data].Options.FStyleID := xlsxStyle_Data;
      StrList.Add( CellXfs2String( FStyleList[xlsxStyle_Data].Options ));
      FStyleList.FCellXfsCount := xlsxDefaultStylesCount;
      for I := xlsxDefaultStylesCount to FStyleList.Count - 1 do
      begin
        j := StrList.IndexOfName( CellXfs2String( FStyleList[I].Options, False ));
        if j > -1 then
          FStyleList[i].Options.FStyleID := j
        else
        begin
          FStyleList[i].Options.FStyleID := FStyleList.FCellXfsCount;
          StrList.Add( CellXfs2String( FStyleList[i].Options ));
          Inc(FStyleList.FCellXfsCount);
        end;
      end;

    finally
      StrList.Free;
    end;

  end;

  procedure SaveFill(UseDefault: Boolean; Color: TColor = clWhite);
  begin
    if UseDefault then
    begin
      Writer.BeginNode('fill');
      Writer.CreateFullNode('patternFill', ['patternType'], ['none']);
      Writer.EndNode();
    end else begin
      Writer.BeginNode('fill');
      Writer.BeginNode('patternFill', ['patternType'], ['solid']);
      Writer.CreateFullNode('fgColor', ['rgb'], [ColorToHex(Color)]);
      Writer.EndNode();
      Writer.EndNode();
    end;
  end;

  procedure CreateBorder(Default: Boolean; Style: TXlsxBorderStyle = xbsThin; Color: TColor = clBlack);
  const
    Borders: array[1..5] of WideString =
      ('left', 'right', 'top', 'bottom', 'diagonal');
  var
    i: Integer;
  begin
    if Default then
    begin
      Writer.BeginNode('border');
      for i := 1 to 5 do
        Writer.CreateFullNode(Borders[i]);
      Writer.EndNode();
    end else
    begin
      Writer.BeginNode('border');
      for i := 1 to 4 do
      begin
        Writer.BeginNode(Borders[i]);
        Writer.AddAttr('style', MSBorderStyleToStr(Style));
        if Color <> clBlack then
          Writer.CreateFullNode('color', ['rgb'], [ColorToHex(Color)])
        else
          Writer.CreateFullNode('color', ['indexed'], ['64']);
        Writer.EndNode();
      end;
      Writer.CreateFullNode(Borders[5]);
      Writer.EndNode();
    end;
  end;

  procedure SaveFont(AFont: TFont);
  begin
    Writer.BeginNode('font');
    if fsBold in AFont.Style then Writer.CreateFullNode('b');
    if fsItalic in AFont.Style then Writer.CreateFullNode('i');
    if fsUnderline in AFont.Style then Writer.CreateFullNode('u');
    Writer.CreateFullNode('sz', ['val'], [IntToStr(AFont.Size)]);
    Writer.CreateFullNode('color', ['rgb'], [ColorToHex(AFont.Color)]);
    Writer.CreateFullNode('name', ['val'], [AFont.Name]);
    Writer.CreateFullNode('charset', ['val'], [IntToStr(AFont.Charset)]);
    Writer.CreateFullNode('family', ['val'], ['2']);
    Writer.CreateFullNode('scheme', ['val'], ['minor']);
    Writer.EndNode();
  end;

  procedure SaveXF( Style: TXlsxCellStyle ); 
  begin
    Writer.BeginNode('xf');
    Writer.AddAttr('numFmtId', IntToStr(Style.FNumFmtId));
    Writer.AddAttr('fontId', IntToStr(Style.FFontId));
    Writer.AddAttr('fillId', IntToStr(Style.FFillId));
    Writer.AddAttr('borderId', IntToStr(Style.FBorderId));
    Writer.AddAttr('xfId','0');
    if Style.FNumFmtId > 0 then
      Writer.AddAttr('applyNumberFormat', '1');
    Writer.AddAttr('applyFont', '1');
    Writer.AddAttr('applyFill', '1');
    Writer.AddAttr('applyBorder', '1');
    if (Style.Alignment <> caLeft)
      or (Style.VerticalAligment <> cvaBottom)
      or Style.WrapText
    then
    begin
      Writer.AddAttr('applyAlignment','1');
      Writer.BeginNode('alignment');
      if Style.Alignment <> caLeft then
        Writer.AddAttr('horizontal',MSCellAlignmentToStr(Style.Alignment));
      if Style.VerticalAligment <> cvaBottom then
        Writer.AddAttr('vertical',MSCellAlignmentToStr(Style.VerticalAligment));
      if Style.WrapText then
         Writer.AddAttr('wrapText','1');
      Writer.EndNode();
    end;
    Writer.EndNode(False);
  end;

begin
  PrepareStyleList();

  Stream := TFileStream.Create(FTempDir + '\xl\styles.xml', fmCreate);
  try
    Writer := TQXMLWriter.Create(Stream);
    try
      Writer.CreateProcessingInstruction('1.0', 'UTF-8', 'yes');
      Writer.BeginNode('styleSheet',['xmlns'], ['http://schemas.openxmlformats.org/spreadsheetml/2006/main']);

      if FStyleList.FNumFmtsCount > 0 then
      begin
        Writer.BeginNode('numFmts');
        Writer.AddAttr('count', IntToStr( FStyleList.FNumFmtsCount ));
        k := xlsxCustomNumericFormat - 1;
        for I := 0 to FStyleList.Count - 1 do
        begin
          if FStyleList[i].Options.FNumFmtId > k then
          begin
            Writer.CreateFullNode('numFmt', ['numFmtId','formatCode'],
              [IntToStr(FStyleList[i].Options.FNumFmtId),
              FStyleList[i].Options.NumericFormat]);
            k := FStyleList[i].Options.FNumFmtId;
          end;
        end;
        Writer.EndNode(); // numFmts
      end;

      Writer.BeginNode('fonts');
      Writer.AddAttr('count', IntToStr( FStyleList.FFontsCount ));
      Writer.BeginNode('font'); //Default
      Writer.CreateFullNode('sz', ['val'], ['11']);
      Writer.CreateFullNode('color', ['theme'], ['1']);
      Writer.CreateFullNode('name', ['val'], ['Calibri']);
      Writer.CreateFullNode('family', ['val'], ['2']);
      Writer.CreateFullNode('charset', ['val'], ['1']); // 1 = DEFAULT_CHARSET  //???
      Writer.CreateFullNode('scheme', ['val'], ['minor']);
      Writer.EndNode(); // font
      k := 0;
      for I := 1 to FStyleList.Count - 1 do
      begin
        if FStyleList[i].Options.FFontId > k then
        begin
          SaveFont(FStyleList[i].Options.Font);
          k := FStyleList[i].Options.FFontId;
        end;
      end;
      Writer.EndNode(); // fonts

      Writer.BeginNode('fills');
      Writer.AddAttr('count', IntToStr( FStyleList.FFillsCount ));
      SaveFill(True); // default
      Writer.BeginNode('fill');
      Writer.CreateFullNode('patternFill', ['patternType'], ['gray125']);
      Writer.EndNode(); // fill
      k := 0;
      for I := 1 to FStyleList.Count - 1 do
      begin
        if FStyleList[i].Options.FFillId > k then
        begin
          SaveFill(not FStyleList[i].Options.UseBackground,
            FStyleList[i].Options.BackgroundColor);
          k := FStyleList[i].Options.FFillId;
        end;
      end;
      Writer.EndNode(); // fills

      Writer.BeginNode('borders');
      Writer.AddAttr('count', IntToStr( FStyleList.FBordersCount ));
      CreateBorder(True); // default
      k := 0;
      for I := 1 to FStyleList.Count - 1 do
      begin
        if FStyleList[i].Options.FBorderId > k then
        begin
          CreateBorder(not FStyleList[i].Options.UseBorder,
            FStyleList[i].Options.Border.Style, FStyleList[i].Options.Border.Color);
          k := FStyleList[i].Options.FBorderId;
        end;
      end;
      Writer.EndNode(); // borders

      Writer.BeginNode('cellStyleXfs', ['count'], ['1']);
      Writer.CreateFullNode('xf', ['numFmtId', 'fontId', 'fillId', 'borderId'], ['0', '0', '0', '0']);
      Writer.EndNode(); // cellStyleXfs

      Writer.BeginNode('cellXfs');
      Writer.AddAttr('count', IntToStr( FStyleList.FCellXfsCount ));
      // default - 0
      Writer.CreateFullNode('xf', ['numFmtId', 'fontId', 'fillId', 'borderId', 'xfId'], ['0', '0', '0', '0', '0']);
      SaveXF(FStyleList[xlsxStyle_Header].Options);
      SaveXF(FStyleList[xlsxStyle_Footer].Options);
      SaveXF(FStyleList[xlsxStyle_Caption].Options);
      SaveXF(FStyleList[xlsxStyle_Data].Options);
      k := 0;
      for I := xlsxDefaultStylesCount to FStyleList.Count - 1 do
      begin
        if FStyleList[i].Options.FStyleId > k then
        begin
          SaveXF(FStyleList[i].Options);
          k := FStyleList[i].Options.FStyleId;
        end;
      end;
      Writer.EndNode(); // cellXfs

      Writer.BeginNode('cellStyles', ['count'], ['1']);
      Writer.CreateFullNode('cellStyle', ['name', 'xfId', 'builtinId'], ['Normal', '0', '0']);
      Writer.EndNode();

      Writer.CreateFullNode('dxfs', ['count'], ['0']);
      Writer.CreateFullNode('tableStyles', ['count', 'defaultTableStyle', 'defaultPivotStyle'], ['0', 'TableStyleMedium9', 'PivotStyleLight16']);
      Writer.CreateFullNode('colors');

      Writer.EndNode(); // sheetStyle
    finally
      Writer.Free;
    end;
  finally
    Stream.Free;
  end;
end;

{ TQExport4Xlsx }

procedure TQExport4Xlsx.SetXlsxOptions(const Value: TQExport4XlsxOptions);
begin
  FXlsxOptions.Assign(Value);
end;

procedure TQExport4Xlsx.SetSheetName(const Value: WideString);
begin
  if Value <> '' then
    FSheetName := Value;
end;

procedure TQExport4Xlsx.WriteHeader;
var
  i: Integer;
begin
  for i := 0 to Header.Count - 1 do
  begin
    Inc(FCounter);
    FXlsxFile.AddCell(0, FCounter, 1, Header[i], ectString, xlsxStyle_Header);
  end;
end;

procedure TQExport4Xlsx.WriteFooter;
var
  i: Integer;
begin
  for i := 0 to Footer.Count - 1 do
  begin
    Inc(FCounter);
    FXlsxFile.AddCell(0, FCounter, 1, Footer[i], ectString, xlsxStyle_Footer);
  end;
end;

procedure TQExport4Xlsx.BeginExport;
begin
  inherited;
  FCounter := 0;
  FDataRowCounter := 0;
  FXlsxFile := TXlsxFileMaker.Create(FileName, XlsxOptions, Formats);
  FXlsxFile.SheetName := SheetName;
  FXlsxFile.CreateDirsStructure;
  if FXlsxFile.IsExistDirsStructure then
  begin
    FXlsxFile.FillCommonData;
    FXlsxFile.PrepareExport;
    WriteHeader;
  end;
end;

procedure TQExport4Xlsx.BeforeExport;
begin
  inherited;

end;

procedure TQExport4Xlsx.AfterExport;
begin

  inherited;
end;

procedure TQExport4Xlsx.EndExport;
begin
  if FXlsxFile.IsExistDirsStructure then
  begin
    WriteFooter;
    FXlsxFile.FinishExport;
    FXlsxFile.CompressFile;
  end;
  FXlsxFile.Free;
  inherited;
end;

procedure TQExport4Xlsx.WriteCaptionRow;
var
  i: Integer;
begin
  if FXlsxFile.IsExistDirsStructure then
  begin
    Inc(FCounter);
    for i := 0 to Columns.Count - 1 do
      FXlsxFile.AddCell(i, FCounter, Columns.Count, GetColCaption(i), ectString, xlsxStyle_Caption);
  end;
end;

procedure TQExport4Xlsx.WriteDataRow;

  function GetValue(Col: TQExportCol): QEString;
  var
    dbl: Double;
    dt: TDateTime;
    d: Integer;
  begin
    //if Col.Data = Null then Result := Formats.NullString
    if VarIsEmpty(Col.Data) or VarIsNull(Col.Data) then
      Result := Formats.NullString
    else
      case Col.Row.Columns[Col.ColumnIndex].ColType of
        ectInteger,
        ectBigint: begin
          try d := Col.Data; except d := 0; end;
          Result := IntToStr(d);
        end;
        ectFloat,
        ectCurrency: begin
          try dbl := Col.Data; except dbl := 0; end;
          Result := FloatToStr(dbl);
        end;
        ectDate,
        ectTime,
        ectDateTime: begin
          try dt := Col.Data; except dt := 0; end;
          Result := FloatToStr(dt);
        end;
        ectString,
        ectBoolean,
        ectUnknown:
          Result := Col.Value;
      end;
  end;

var
  i, n: Integer;
begin
  if FXlsxFile.IsExistDirsStructure then
  begin
    Inc(FCounter);
    Formats.StoreSeparators;
    Formats.DecimalSeparator := '.'; 
    Formats.ApplyParams;
    for i := 0 to ExportRow.Count - 1 do
    begin
      n := xlsxStyle_Data;
      if (FXlsxFile.FStyleList.Count > xlsxDefaultStylesCount) then
        if (XlsxOptions.StripStylesList.Count > 0) and (XlsxOptions.StripStyleType <> ssNone) then
          if (XlsxOptions.StripStyleType = ssRow) then
          begin
            n := (FDataRowCounter mod XlsxOptions.StripStylesList.Count)
              * ExportRow.Count + I + xlsxDefaultStylesCount;
            n := FXlsxFile.FStyleList[n].Options.StyleId;
          end
          else
            n := FXlsxFile.FStyleList.StyleIdByColName(ExportRow.Columns[i].Name)
        else
        if not (ExportRow.Columns[i].ColType in [ectString, ectBoolean, ectUnknown]) then
          n := FXlsxFile.FStyleList.StyleIdByColName(ExportRow.Columns[i].Name);

      FXlsxFile.AddCell(i, FCounter, ExportRow.Count, GetValue(ExportRow[i]),
        ExportRow.Columns[i].ColType, n );
    end;
    Formats.RestoreSeparators;
    Inc(FDataRowCounter);
  end;
end;

constructor TQExport4Xlsx.Create(AOwner: TComponent);
begin
  inherited;
  FSheetName := 'sheet1';
  FXlsxOptions := TQExport4XlsxOptions.Create(Self);
end;

destructor TQExport4Xlsx.Destroy;
begin
//  if Assigned(FXlsxOptions) then // dee
//  begin
    FXlsxOptions.Free;
    FXlsxOptions := nil;
//  end;
  inherited;
end;

procedure TQExport4Xlsx.Execute;
begin
  DoExport;
  ShowResult;
end;

initialization
  xlsxDefaultFont := TFont.Create();
  xlsxDefaultFont.Name := 'Calibri';
  xlsxDefaultFont.Size := 11;
  xlsxDefaultFont.Style := [];
  xlsxDefaultFont.Color := clBlack;

finalization
  xlsxDefaultFont.Free();

end.
